package com.byxy.dao;

import com.byxy.model.Department;
import com.byxy.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDaoImpl implements BaseDao<Department> {
	private PreparedStatement psta = null;
	private Connection conn = null;
	private ResultSet rs = null;
	
	@Override
	public int add(Department dePM) {
		int i = -1;
		int id = dePM.getId();
		String name = dePM.getName();
		String sql = "INSERT INTO Department VALUES(?, ?)";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setInt(1, id);
			psta.setString(2, name);
			i = psta.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}

	@Override
	public int deleteById(int id) {
		int i = -1;
		String sql = "DELETE FROM Department WHERE id = ?";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setInt(1, id);
			i = psta.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}

	@Override
	public int update(Department dePM) {
		int i = -1;
		int id = dePM.getId();
		String name = dePM.getName();
		String sql = "UPDATE Department SET Name = ? WHERE id = ?";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setString(1, name);
			psta.setInt(2, id);
			i = psta.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}

	@Override
	public List<Department> findAll() {
		List<Department> list = new ArrayList<Department>();
		String sql = "SELECT * FROM Department";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			rs = psta.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				list.add(new Department(id, name));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(rs, psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public Department findById(int id) {
		Department dePM = null;
		String sql = "SELECT * FROM Department WHERE id = ?";
		try {
			conn = DBUtil.getConnection();
			psta = conn.prepareStatement(sql);
			psta.setInt(1, id);
			rs = psta.executeQuery();
			while (rs.next()) {
				int uid = rs.getInt("id");
				String name = rs.getString("name");
				dePM = new Department(uid ,name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(rs, psta, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return dePM;
	}

}
